
--drop assembly objects (links)
DECLARE @sql_error INT
        , @nsql NVARCHAR(MAX)
        , @assembly_name VARCHAR(260)

SELECT @assembly_name = 'UtilityXml'
    , @sql_error = NULL
    , @nsql = ''

--http://msdn2.microsoft.com/en-us/library/ms190324.aspx
SELECT @nsql = @nsql
	+ 'DROP ' + CASE WHEN o.[type] IN ('AF', 'FS', 'FT')
		THEN 'FUNCTION'
		WHEN o.[type] IN ('TA')
		THEN 'TRIGGER'
		WHEN o.[type] IN ('PC')
		THEN 'PROCEDURE'
		END
	+ ' '
	+ CASE WHEN SCHEMA_NAME(o.[schema_id]) IS NOT NULL
		THEN QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.'
		ELSE ''
		END
	+ QUOTENAME(o.[name])
	+ CHAR(13) + CHAR(10)
FROM sys.assemblies AS a
JOIN sys.assembly_modules AS am
ON am.assembly_id = a.assembly_id
JOIN sys.objects AS o
ON o.[object_id] = am.[object_id]
WHERE a.[name] = @assembly_name

EXECUTE(@nsql)

IF @@ERROR <> 0
BEGIN
	RAISERROR('Failed to drop assembly objects.', 16, 1)
END
